create or replace
PACKAGE BODY SQL_STORE AS

       --生成的job调用此处理过程
          PROCEDURE job_detail IS       
           table_exist NUMBER;       --存储表mc$sql_time是否存在指示器
           job_result_t_exist NUMBER;--存储JOB执行结果的表是否存在
           d_sql VARCHAR2(600);      --存放临时SQL
           synced_snap NUMBER;       --每次同步后快照id，存储至结果表mc$sql_time_result
           target_snap NUMBER;        --源表wrh$_sqlstat中最大的快照id
           local_snap NUMBER;        --本地mc$sql_time中最大的快照id
           synced_count NUMBER;         --每次同步的数据行数，存储至mc$sql_time_result表中
           type sync_dbs_t is table of varchar2(100);
           sync_dbs sync_dbs_t;
           inst_num NUMBER;
           db_id NUMBER;
          BEGIN
            local_snap:=0;
            target_snap:=0;
            synced_count:=0;
            --取得管理库中配置的所有dblink
            SELECT dbname BULK COLLECT INTO sync_dbs FROM mc$lg_verifymode;
            --遍历所有dblink，取数据
            FOR i IN sync_dbs.FIRST..sync_dbs.LAST
            LOOP
              BEGIN   --使用子程序包裹，如果一个dblink不能连接，不影响其他dblink
              SELECT count(*) INTO table_exist FROM user_tables WHERE table_name='MC$SQL_TIME';
              IF table_exist=0 THEN 
                 --如果没有存储表，则创建之     
                 d_sql:='CREATE  TABLE mc$sql_time PARTITION BY HASH(snap_id)(PARTITION p1,PARTITION p2,PARTITION p3,PARTITION p4) AS SELECT * FROM (SELECT s.*,row_number() OVER(PARTITION BY snap_id ORDER BY elapsed_time_delta DESC) rn FROM sys.wrh$_sqlstat@'||sync_dbs(i)||' s) t WHERE snap_id=(SELECT MAX(snap_id) FROM sys.wrh$_sqlstat@'||sync_dbs(i)||') AND rn<=50';
                 --dbms_output.put_line('job_detail:'||d_sql);
                 EXECUTE IMMEDIATE d_sql;
                 EXECUTE IMMEDIATE 'SELECT instance_number FROM v$instance@'||sync_dbs(i) INTO inst_num;
                 --dbms_output.put_line('job_detail:SELECT dbid FROM mc$lg_verifymode WHERE dbname='''||sync_dbs(i)||'''');
                 EXECUTE IMMEDIATE 'SELECT dbid FROM mc$lg_verifymode WHERE dbname='''||sync_dbs(i)||'''' INTO db_id;
                 EXECUTE IMMEDIATE 'SELECT count(*) FROM mc$sql_time WHERE dbid='||db_id||' AND instance_number='||inst_num INTO synced_count;
                 
              END IF;
              --dbms_output.put_line('job_detail:'||i||':'||sync_dbs(i));
              EXECUTE IMMEDIATE 'SELECT instance_number FROM v$instance@'||sync_dbs(i) INTO inst_num;
              --dbms_output.put_line('job_detail:SELECT dbid FROM mc$lg_verifymode WHERE dbname='''||sync_dbs(i)||'''');
              EXECUTE IMMEDIATE 'SELECT dbid FROM mc$lg_verifymode WHERE dbname='''||sync_dbs(i)||'''' INTO db_id;
              
              EXECUTE IMMEDIATE 'SELECT max(snap_id) FROM mc$sql_time WHERE dbid='||db_id||' AND instance_number='||inst_num INTO local_snap;
              EXECUTE IMMEDIATE 'SELECT max(snap_id) FROM sys.wrh$_sqlstat@'||sync_dbs(i) INTO target_snap;
              
              IF local_snap IS NULL THEN
                 local_snap:=target_snap-1;
              END IF;
            
              IF local_snap < target_snap THEN  --snap不同步，需要更新
                 --开始同步数据，取wrh$_sqlstat中(local_snap,target_snap]之间快照，每个快照取最多50条SQL信息
                 
                 d_sql:='INSERT INTO mc$sql_time SELECT * FROM (SELECT s.*,row_number() OVER(PARTITION BY snap_id ORDER BY elapsed_time_delta DESC) rn FROM sys.wrh$_sqlstat@'||sync_dbs(i)||' s) t WHERE rn<=50 and snap_id BETWEEN '||(local_snap+1)||' AND '||target_snap;
                 EXECUTE IMMEDIATE 'SELECT count(*) FROM (SELECT s.*,row_number() OVER(PARTITION BY snap_id ORDER BY elapsed_time_delta DESC) rn FROM sys.wrh$_sqlstat@'||sync_dbs(i)||' s) t WHERE rn<=50 and snap_id BETWEEN ('||(local_snap+1)||') AND '||target_snap INTO synced_count;
            
                 EXECUTE IMMEDIATE d_sql;
              END IF;
              --job运行情况记录，存储至MC$SQL_TIME_RESULT
              SELECT count(*) INTO job_result_t_exist FROM user_tables WHERE table_name='MC$SQL_TIME_RESULT';
              IF job_result_t_exist=0 THEN
                 d_sql:='CREATE TABLE mc$sql_time_result(job_run_time DATE,synced_snap NUMBER,synced_count NUMBER,local_snap NUMBER,target_snap NUMBER,dbid NUMBER,instance_number NUMBER,instance_name VARCHAR2(100))';
                 EXECUTE IMMEDIATE d_sql;
              END IF;  
              
                      
              EXECUTE IMMEDIATE 'SELECT max(snap_id) FROM mc$sql_time WHERE dbid='||db_id||' AND instance_number='||inst_num INTO synced_snap;           
              d_sql:='INSERT INTO mc$sql_time_result VALUES(SYSDATE,'||synced_snap||','||synced_count||','||local_snap||','||target_snap||','||db_id||','||inst_num||','''||sync_dbs(i)||''')';     
              dbms_output.put_line(d_sql);
              EXECUTE IMMEDIATE d_sql;
              COMMIT;
             -- EXCEPTION
               -- WHEN others THEN
                --  dbms_output.put_line('job_detail:'||SQLERRM);
              END;
             END LOOP;
       END;
       --提交job,间隔时间使用awr的间隔时间+15分钟
       PROCEDURE create_job(success OUT BOOLEAN) IS    
          jobno NUMBER;
          awr_interval INTERVAL DAY TO SECOND;  --设置job执行的间隔时间为AWR间隔时间
         
          job_exist      NUMBER;
          type awr_interval_second_t is table of NUMBER;
          type sync_dbs_t is table of varchar2(100);
          sync_dbs sync_dbs_t;
          awr_interval_second awr_interval_second_t:= awr_interval_second_t();
          awr_interval_min NUMBER;
         BEGIN
            awr_interval_min:=0;
            SELECT dbname BULK COLLECT INTO sync_dbs FROM mc$lg_verifymode;
            --遍历所有dblink，取数据
            FOR i IN sync_dbs.FIRST..sync_dbs.LAST
            LOOP
              BEGIN
                SELECT count(*) INTO job_exist FROM dba_jobs WHERE what='sql_store.job_detail();'; 
                IF job_exist=0 THEN
                   EXECUTE IMMEDIATE 'SELECT snap_interval FROM DBA_HIST_WR_CONTROL@'||sync_dbs(i) INTO awr_interval;
                   awr_interval_second.extend;
                   awr_interval_second.extend;
                   --快照间隔INTERVAL转化为SECOND(NUMBER)
                   awr_interval_second(i) := to_number(extract(second from awr_interval)) +
                   to_number(extract(minute from awr_interval)) * 60 + 
                   to_number(extract(hour from awr_interval))   * 60 * 60 + 
                   to_number(extract(day from awr_interval))  * 60 * 60* 24;
                   IF awr_interval_second(i)>awr_interval_min THEN
                     awr_interval_min:=awr_interval_second(i);
                   END IF;
                   
                   IF awr_interval_min>=3600 THEN
                      dbms_job.submit(jobno,'sql_store.job_detail();',trunc(sysdate,'HH')+5/60/24,'trunc(sysdate,''HH'')+5/60/24+'||awr_interval_min||'/24/60/60');
                   ELSE
                      dbms_job.submit(jobno,'sql_store.job_detail();',trunc(sysdate,'MI')+5/60/24,'trunc(sysdate,''MI'')+5/60/24+'||awr_interval_min||'/24/60/60');
                   END IF;
                   success:=TRUE;
                   COMMIT;
                 ELSE
                   success:=FALSE;
                 END IF;
                 EXCEPTION
                 WHEN others THEN
                  success:=FALSE;
                END;
             END LOOP;
             EXCEPTION
               WHEN others THEN
                 success:=FALSE;
       END;
       --删除job
       PROCEDURE delete_job(success OUT BOOLEAN) IS   
         jobno NUMBER;
         BEGIN
           SELECT job INTO jobno FROM dba_jobs WHERE what='sql_store.job_detail();';
           dbms_job.remove(jobno);
           COMMIT;
           success:=TRUE;
           EXCEPTION
             WHEN others THEN
               success:=FALSE;
        END;
         
        PROCEDURE drop_table IS
          BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE mc$sql_time';
            EXECUTE IMMEDIATE 'DROP TABLE mc$sql_time_result';
            COMMIT;
            EXCEPTION
              WHEN others THEN
                dbms_output.put_line(SQLERRM);
        END; 
        --前台读取数据的API，用于返回一个API
        FUNCTION data_cursor RETURN SYS_REFCURSOR IS
          data_cur SYS_REFCURSOR;
          cur_sql VARCHAR2(200);
          BEGIN
            cur_sql:='SELECT * FROM mc$sql_time';
            OPEN data_cur for cur_sql;
            RETURN data_cur;
        END;
          
END sql_store;

